/*********************************************************************************
 *      Copyright:  (C) 2022 Zhang xiangyun<1327295677@qq.com>
 *                  All rights reserved.
 *
 *       Filename:  database.c
 *    Description:  This file 
 *                 
 *        Version:  1.0.0(25/04/22)
 *         Author:  Zhang xiangyun <1327295677@qq.com>
 *      ChangeLog:  1, Release initial version on "25/04/22 10:35:25"
 *                 
 ********************************************************************************/

#include <stdio.h>
#include <sqlite3.h>
#include <string.h>
#include <time.h>
#include <stdlib.h>
#include "logger.h"
#include "pack.h"
#define TABLENAME "Raspberrypi"
sqlite3 *db;
//int sqlit_init(sqlite3 **db);
int sqlite_create_database(void);
int sqlite_data_insert(pack_info_t pack_info);
int sqlite_select_firstdata(pack_info_t *pack_info);
int empty_table_determine(void);
int sqlite_data_delete(void);



int sqlite_create_database(void)
{
	int      rc =-1;
	char    *zErrMsg =0;
	char     sql[100];
	int      exist=0;

	rc=sqlite3_open("temperature.db",&db);
	if(rc!=0)
	{
		log_error("open database failure:%s\n");
		return -2;
	}
	else
	{
		log_info("open database success!\n");
	}

	memset(sql,0,sizeof(sql));
	sprintf(sql,"CREATE TABLE IF NOT EXISTS %s(SN CHAR(50),TIME CHAR(50),TEMP CHAR(10));",TABLENAME);

	rc=sqlite3_exec(db,sql,NULL,0,&zErrMsg);

	if(rc!=SQLITE_OK)
	{
		log_error("sqlite_create_table failure:%s",zErrMsg);
		sqlite3_free(zErrMsg);
		return -1;
	}
	else 
	{
		log_debug("create table success!\n");
	}
}

/* insert data to table */
int sqlite_data_insert(pack_info_t pack_info)
{
	char      sql[512];
	int       rc=-1;
	char     *zErrMsg=0;


	memset(sql,0,sizeof(sql));
	sprintf(sql,"INSERT INTO %s VALUES ('%s','%s','%.2f');",TABLENAME,pack_info.serial_id,pack_info.sample_time,pack_info.temper);
	//printf("sql:%s\n",sql);

	rc=sqlite3_exec(db,sql,NULL,0,&zErrMsg);
	if(rc!=SQLITE_OK)
	{
		log_error("sqlite insert error:%s\n",zErrMsg);
		sqlite3_free(zErrMsg);
		return -2;
	}
	else
	{
		log_info("data insert into database success!\n");
	}
	return 0;

}

/*selecet the first data from database table  */
int sqlite_select_firstdata(pack_info_t *pack_info)
{
	char      sql[512];
	int       rc=-1;
	char     *zErrMsg=0;
	char    **dbResult;
	int       nRow=0,nColumn=0;

	
	memset(sql,0,sizeof(sql));
	sprintf(sql,"select * from %s where rowid in(select rowid from %s LIMIT 1);",TABLENAME,TABLENAME);

	rc=sqlite3_get_table(db,sql,&dbResult,&nRow,&nColumn,&zErrMsg);
	if(rc!=SQLITE_OK)
	{
		log_error("select first data failure:%s",zErrMsg);
		sqlite3_free(zErrMsg);
		return -2;
	}
	else
	{
		log_debug("select first data from database success!\n");
	}

	strncpy(pack_info->serial_id,dbResult[3],DEVID_LEN);
	strncpy(pack_info->sample_time,dbResult[4],TIME_LEN);
	strncpy(pack_info->temper,dbResult[5],256);


	return 0;

}


/* determine whether the table is empty */
int empty_table_determine(void)
{
	char      sql[512];
	int       rc=-1;
	char     *zErrMsg=0;
	char    **dbResult;
	int       nRow=0,nColumn=0;


	memset(sql,0,sizeof(sql));
	sprintf(sql,"select rowid from %s",TABLENAME);

	rc=sqlite3_get_table(db,sql,&dbResult,&nRow,&nColumn,&zErrMsg);
	if(rc != SQLITE_OK)
	{
		log_error("excute empty_table)_determine failure:%s\n",zErrMsg);
		sqlite3_free(zErrMsg);
		return -2;
	}
	log_debug("nrow:%d\n",nRow);
	return nRow;
}


/* Delete data in table */
int sqlite_data_delete(void)
{
	char        sql[100];
	int         rc=-1;
	char       *zErrMsg=0;


	memset(sql,0,sizeof(sql));
	sprintf(sql,"DELETE from %s where rowid in(select min(rowid) from %s)",TABLENAME,TABLENAME);
	rc=sqlite3_exec(db,sql,0,0,&zErrMsg);
	if(rc!=SQLITE_OK)
	{
		log_error("exert sqlite_data_delete error:%s\n",zErrMsg);
		sqlite3_free(zErrMsg);
		return -2;
	}
	else
	{
		log_debug("delete first data success!\n");
		return 0;
	}
}

